import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ttq
 * Date: 13-8-8
 * Time: 上午10:58
 * To change this template use File | Settings | File Templates.
 */
public class PersonDaoImpl extends JdbcDaoSupport implements IPersonDao {
    public void initDatabase() {
        String sql = " create table if not exists tb_person "
                + " (id int auto_increment," + " name varchar(255),"
                + " sex varchar(10) ,age int ,birthday timestamp,primary key(id))";
        getJdbcTemplate().execute(sql);
    }

    @Override
    public String getPersonName(Integer id) {
        String sql = "select name from tb_person where id=" + id;
        return (String) getJdbcTemplate().queryForObject(sql, String.class);
    }

    @Override
    public void addPerson(Person person) {
        //To change body of implemented methods use File | Settings | File Templates.
        String sql = " insert into tb_person(name,sex,age,birthday)values(?,?,?,?)";
        getJdbcTemplate().update(sql, new Object[]{person.getName(), person.getSex(), person.getAge(), person.getBirthday()});
    }

    @Override
    public int getPersonsCount() {
        String sql = " select count(*) from tb_person ";
        return getJdbcTemplate().queryForInt(sql);
    }


    @Override
    @SuppressWarnings("all")
    public List<Person> listPersons() {
        String sql = "select id ,name,sex,age,birthday from tb_person";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql);
        List<Person> personList = new ArrayList<Person>();
        for (Map<String, Object> row : list) {
            Person person = new Person();
            person.setId((Integer) row.get("id"));
            person.setName((String) row.get("name"));
            person.setSex((String) row.get("sex"));
            person.setAge((Integer) row.get("age"));
            person.setBirthday((Date) row.get("birthday"));

            personList.add(person);
        }
        return personList;
    }

    @Override
    public List finAllPersons() {
        PersonMappingSqlQuery personMappingSqlQuery = new PersonMappingSqlQuery();
        personMappingSqlQuery.setDataSource(getDataSource());
        personMappingSqlQuery.setSql("select * from tb_person");
        personMappingSqlQuery.compile();
        return personMappingSqlQuery.execute(new Object[]{});
    }
}
